
-- ============
-- drop tables
IF OBJECT_ID('ssf.dpnd_object_comment') IS NOT NULL
	DROP TABLE ssf.dpnd_object_comment
GO
IF OBJECT_ID('ssf.dpnd_dependency_object') IS NOT NULL
	DROP TABLE ssf.dpnd_dependency_object
GO

IF OBJECT_ID('ssf.dpnd_header_parameter') IS NOT NULL
	DROP TABLE ssf.dpnd_header_parameter
GO

IF OBJECT_ID('ssf.dpnd_header_object') IS NOT NULL
	DROP TABLE ssf.dpnd_header_object
GO
-- ============

-- ============
-- create tables
CREATE TABLE ssf.dpnd_header_object (
	header_object_id INT IDENTITY(1,1) NOT NULL
	, dpnd_object_name NVARCHAR(256) NOT NULL
	, object_type NVARCHAR(128) NOT NULL
	, purpose NVARCHAR(MAX) NULL
	, return_data_type NVARCHAR(128) DEFAULT 'INT' NOT NULL
	, create_date SMALLDATETIME NULL
	, create_user NVARCHAR(128) NULL
)
GO

CREATE TABLE ssf.dpnd_header_parameter (
	parameter_id INT IDENTITY(1,1) NOT NULL
	, header_object_id INT NOT NULL
	, parameter_order SMALLINT NOT NULL
	, parameter_name NVARCHAR(128) NOT NULL
	, parameter_desc NVARCHAR(2048) NULL
	, data_type NVARCHAR(128) NOT NULL
	, default_value NVARCHAR(512) NULL
	, return_type VARCHAR(16) DEFAULT 'INPUT' NOT NULL
)
GO

CREATE TABLE ssf.dpnd_dependency_object (
	dpnd_object_id INT IDENTITY(1,1) NOT NULL
	, header_object_id INT NOT NULL
	, dependency_type VARCHAR(128) NOT NULL
	, dpnd_object_name NVARCHAR(512) NOT NULL
	, object_type NVARCHAR(128) NOT NULL
	, change_type NVARCHAR(128) NULL
)
GO

CREATE TABLE ssf.dpnd_object_comment (
	object_comment_id INT IDENTITY(1,1) NOT NULL
	, header_object_id INT NOT NULL
	, comment_sequence INT NOT NULL
	, code_comment NVARCHAR(MAX) NULL
)
GO
-- ============

-- ============
-- create primary keys
ALTER TABLE ssf.dpnd_dependency_object
ADD CONSTRAINT PK_dpnd_object
PRIMARY KEY CLUSTERED (dpnd_object_id)
GO

ALTER TABLE ssf.dpnd_header_object
ADD CONSTRAINT PK_header_object
PRIMARY KEY CLUSTERED (header_object_id)
GO

ALTER TABLE ssf.dpnd_header_parameter
ADD CONSTRAINT PK_dpnd_parameter
PRIMARY KEY CLUSTERED (parameter_id)
GO

ALTER TABLE ssf.dpnd_object_comment
ADD CONSTRAINT PK_dpnd_object_comment
PRIMARY KEY CLUSTERED (object_comment_id)
GO
-- ============

-- ============
-- create indexes 
ALTER TABLE ssf.dpnd_header_object
ADD CONSTRAINT UQ_dpnd_header_object
UNIQUE (dpnd_object_name)
GO

ALTER TABLE ssf.dpnd_header_parameter
ADD CONSTRAINT UQ_dpnd_header_parameter
UNIQUE (header_object_id, parameter_name)
GO

ALTER TABLE ssf.dpnd_object_comment
ADD CONSTRAINT UQ_dpnd_object_comment
UNIQUE (header_object_id, comment_sequence)
GO

CREATE NONCLUSTERED INDEX ndx_dependency_object
ON ssf.dpnd_dependency_object (header_object_id, dependency_type)
INCLUDE (dpnd_object_name)
GO
-- ============

-- ============
-- create foreign keys
ALTER TABLE ssf.dpnd_dependency_object
ADD CONSTRAINT FK_dpnd_dependency_object_header_object_id
FOREIGN KEY (header_object_id)
REFERENCES ssf.dpnd_header_object (header_object_id)
GO

ALTER TABLE ssf.dpnd_header_parameter
ADD CONSTRAINT FK_dpnd_header_parameter_header_object_id
FOREIGN KEY (header_object_id)
REFERENCES ssf.dpnd_header_object (header_object_id)
GO

ALTER TABLE ssf.dpnd_object_comment
ADD CONSTRAINT FK_dpnd_object_comment_header_object_id
FOREIGN KEY (header_object_id)
REFERENCES ssf.dpnd_header_object (header_object_id)
GO
-- ============

-- ============
-- add extended properties
EXEC sp_addextendedproperty 'MS_Description', 'Describes an object dependency for a header object.', 'schema', ssf, 'table', dpnd_dependency_object
GO
EXEC sp_addextendedproperty 'MS_Description', 'IDENTITY column.', 'schema', ssf, 'table', dpnd_dependency_object, 'column', dpnd_object_id
GO

EXEC sp_addextendedproperty 'MS_Description', 'Identifies the header object that is dependent on this object.', 'schema', ssf, 'table', dpnd_dependency_object, 'column', header_object_id
GO

EXEC sp_addextendedproperty 'MS_Description', 'Type of dependency.', 'schema', ssf, 'table', dpnd_dependency_object, 'column', dependency_type
GO

EXEC sp_addextendedproperty 'MS_Description', 'Name of dependency object.', 'schema', ssf, 'table', dpnd_dependency_object, 'column', dpnd_object_name
GO

EXEC sp_addextendedproperty 'MS_Description', 'Type of dependency object.', 'schema', ssf, 'table', dpnd_dependency_object, 'column', object_type
GO

EXEC sp_addextendedproperty 'MS_Description', 'Type of change made to the object.', 'schema', ssf, 'table', dpnd_dependency_object, 'column', change_type
GO

EXEC sp_addextendedproperty 'MS_Description', 'Describes an object that has a header.', 'schema', ssf, 'table', dpnd_header_object
GO
EXEC sp_addextendedproperty 'MS_Description', 'IDENTITY column.', 'schema', ssf, 'table', dpnd_header_object, 'column', header_object_id
GO

EXEC sp_addextendedproperty 'MS_Description', 'Name of the object.', 'schema', ssf, 'table', dpnd_header_object, 'column', dpnd_object_name
GO

EXEC sp_addextendedproperty 'MS_Description', 'Type of object.', 'schema', ssf, 'table', dpnd_header_object, 'column', object_type
GO

EXEC sp_addextendedproperty 'MS_Description', 'Purpose of the object.', 'schema', ssf, 'table', dpnd_header_object, 'column', purpose
GO

EXEC sp_addextendedproperty 'MS_Description', 'Return type of header object.', 'schema', ssf, 'table', dpnd_header_object, 'column', return_data_type
GO

EXEC sp_addextendedproperty 'MS_Description', 'Date object was created.', 'schema', ssf, 'table', dpnd_header_object, 'column', create_date
GO

EXEC sp_addextendedproperty 'MS_Description', 'Network username of the author of the object.', 'schema', ssf, 'table', dpnd_header_object, 'column', create_user
GO

EXEC sp_addextendedproperty 'MS_Description', 'Describes parameters for a header object.', 'schema', ssf, 'table', dpnd_header_parameter
GO
EXEC sp_addextendedproperty 'MS_Description', 'IDENTITY column.', 'schema', ssf, 'table', dpnd_header_parameter, 'column', parameter_id
GO

EXEC sp_addextendedproperty 'MS_Description', 'Identifies the header object that uses the parameter.', 'schema', ssf, 'table', dpnd_header_parameter, 'column', header_object_id
GO

EXEC sp_addextendedproperty 'MS_Description', 'Order of the paramter.', 'schema', ssf, 'table', dpnd_header_parameter, 'column', parameter_order
GO

EXEC sp_addextendedproperty 'MS_Description', 'Name of the paramter.', 'schema', ssf, 'table', dpnd_header_parameter, 'column', parameter_name
GO

EXEC sp_addextendedproperty 'MS_Description', 'Description of the parameter.', 'schema', ssf, 'table', dpnd_header_parameter, 'column', parameter_desc
GO

EXEC sp_addextendedproperty 'MS_Description', 'Data type of the parameter.', 'schema', ssf, 'table', dpnd_header_parameter, 'column', data_type
GO

EXEC sp_addextendedproperty 'MS_Description', 'Default value of the parameter.', 'schema', ssf, 'table', dpnd_header_parameter, 'column', default_value
GO

EXEC sp_addextendedproperty 'MS_Description', 'Return type of the parameter, INPUT, OUTPUT or INPUT/OUTPUT.', 'schema', ssf, 'table', dpnd_header_parameter, 'column', return_type
GO

EXEC sp_addextendedproperty 'MS_Description', 'Sequential comments for header objects.', 'schema', ssf, 'table', dpnd_object_comment
GO
EXEC sp_addextendedproperty 'MS_Description', 'IDENTITY column.', 'schema', ssf, 'table', dpnd_object_comment, 'column', object_comment_id
GO

EXEC sp_addextendedproperty 'MS_Description', 'Identifies the header object the comments relate to.', 'schema', ssf, 'table', dpnd_object_comment, 'column', header_object_id
GO

EXEC sp_addextendedproperty 'MS_Description', 'Sequence of the comment in the header object''s code.', 'schema', ssf, 'table', dpnd_object_comment, 'column', comment_sequence
GO

EXEC sp_addextendedproperty 'MS_Description', 'A comment on the code.', 'schema', ssf, 'table', dpnd_object_comment, 'column', code_comment
GO
-- ============
